//
//  DBDataBase.m
//  UI_数据库
//
//  Created by dllo on 15/8/14.
//  Copyright (c) 2015年 蓝鸥科技. All rights reserved.
//

#import "DBDataBase.h"
#import "DB.h"
#import "VoiceMusicModel.h"

@implementation DBDataBase

+ (BOOL)insertWithmodel:(VoiceMusicModel *)model
{
    // 第一步: 获得数据库指针
    sqlite3 *db = [DB opensqlitDB];
    // 第二部: 获得数据库语句
    NSString *str = [NSString stringWithFormat:@"insert into mytable(tingid, title, musicUrl, coverimg) values ('%@', '%@', '%@', '%@')", model.tingid, model.title, model.musicUrl, model.coverimg];
    
    // 执行语句
    int result = sqlite3_exec(db, [str UTF8String], nil, nil, nil);
    
    if (result == SQLITE_OK) {
        return YES;
    }
    else {
        return NO;
    }
}

// 1. 查询所有的 2. 查询单个

+ (NSArray *)selectAll
{
    // 获取数据库指针
    sqlite3 *db = [DB opensqlitDB];
    // 定义数据库替身
    sqlite3_stmt *stmt = nil;
    
    // 数据库查询语句
    NSString *str = [NSString stringWithFormat:@"select tingid, title, musicUrl, coverimg from mytable"];
    
    // 执行语句
    int results = sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (results == SQLITE_OK) {
        NSLog(@"成功");
        
        // 判断是否有据用于下一行的数读取
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *tingid = sqlite3_column_text(stmt, 0);
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *musicUrl = sqlite3_column_text(stmt, 2);
            const unsigned char *coverimg = sqlite3_column_text(stmt, 3);
            
            // 将这一行数据存入model中
            NSString *tingid1 =[NSString stringWithUTF8String:(const char *)tingid];
            NSString *title1 = [NSString stringWithUTF8String:(const char *)title];
            NSString *musicUrl1 = [NSString stringWithUTF8String:(const char *)musicUrl];
            NSString *coverimg1 = [NSString stringWithUTF8String:(const char *)coverimg];
            
            VoiceMusicModel *model = [[VoiceMusicModel alloc] init];
            model.title = title1;
            model.tingid = tingid1;
            model.musicUrl = musicUrl1;
            model.coverimg = coverimg1;
            [array addObject:model];
        }
    }
    sqlite3_finalize(stmt);
    return array;
}

+ (VoiceMusicModel *)selectWithId:(NSString *)modelId
{
    // 获取数据库指针
    sqlite3 *db = [DB opensqlitDB];
    
    // 定义数据库替身
    sqlite3_stmt *stmt = nil;
    
    // 数据库查询语句
    NSString *str = [NSString stringWithFormat:@"select tingid, title, musicUrl, coverimg from mytable where tingid = '%@'", modelId];
    
    // 执行语句
    int results = sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, nil);
    
    if (results == SQLITE_OK) {
        NSLog(@"成功");
        
        // 判断是否有据用于下一行的数读取
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *tingid = sqlite3_column_text(stmt, 0);
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *musicUrl = sqlite3_column_text(stmt, 2);
            const unsigned char *coverimg = sqlite3_column_text(stmt, 3);
            
            // 将这一行数据存入model中
            NSString *tingid1 =[NSString stringWithUTF8String:(const char *)tingid];
            NSString *title1 = [NSString stringWithUTF8String:(const char *)title];
            NSString *musicUrl1 = [NSString stringWithUTF8String:(const char *)musicUrl];
            NSString *coverimg1 = [NSString stringWithUTF8String:(const char *)coverimg];
            
            VoiceMusicModel *model = [[VoiceMusicModel alloc] init];
            model.title = title1;
            model.tingid = tingid1;
            model.musicUrl = musicUrl1;
            model.coverimg = coverimg1;
            return model;
        }
    }
    sqlite3_finalize(stmt);
    return nil;
}

+ (BOOL)selectWithModel:(VoiceMusicModel *)model
{
    // 获取数据库指针
    sqlite3 *db = [DB opensqlitDB];
    
    // 获取数据库语句
    NSString *str = [NSString stringWithFormat:@"select * from mytable where tingid = '%@'", model.tingid];
    // 执行语句
    int results = sqlite3_exec(db, [str UTF8String], nil, nil, nil);
    
    if (results == SQLITE_OK) {
        return YES;
    }
    else {
        return NO;
    }
}


+ (void)deleteWith:(VoiceMusicModel *)model
{
    // 获取指针
    
    sqlite3 *db = [DB opensqlitDB];
    
    // 获取数据库语句
    NSString *str = [NSString stringWithFormat:@"delete from mytable where tingid = '%@'", model.tingid];
    
    // 执行数据库语句
    int reuselts = sqlite3_exec(db, [str UTF8String], nil, nil, nil);
    if (reuselts == SQLITE_OK) {
        NSLog(@"成功");
    }
    else {
        NSLog(@"失败");
    }
}


+ (void)upateWithModel:(VoiceMusicModel *)model
{
    // 获取指针
    sqlite3 *db = [DB opensqlitDB];
    NSString *str = [NSString stringWithFormat:@"update mytable set a_name = '%@' where a_id = '%@'", model.title, model.coverimg];
    
    int reuselts = sqlite3_exec(db, [str UTF8String], nil, nil, nil);
    if (reuselts == SQLITE_OK) {
        NSLog(@"成功");
    }
    else {
        NSLog(@"失败");
    }
}
@end
